﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace WebApplication1
{
    public static class SqlHelper
    {
        private static string connStr = "server=.;" +
            "database=Student_db;uid=sa;pwd=123456";

        /// <summary>
        /// 使用ExcuteReader方法查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns>返回SqlDataReader</returns>
        public static SqlDataReader ExcuteReader(string sql, params SqlParameter[] p)
        {
            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (p != null)
            {
                cmd.Parameters.AddRange(p);
            }
            try
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                //关闭reader是也关闭sqlconnection
                return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }
        }

        /// <summary>
        /// 使用adapter和DataTable方法查询
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns>返回数据表</returns>
        public static DataTable ExcuteDataTable(string sql, params SqlParameter[] p)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                    {
                        if (p != null)
                        {
                            adapter.SelectCommand.Parameters.AddRange(p);
                        }
                        DataTable table = new DataTable();
                        if (conn.State == ConnectionState.Closed)
                        {
                            conn.Open();
                        }
                        adapter.Fill(table);
                        return table;
                    }
                }
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }

        }

        /// <summary>
        /// 执行非查询语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="p"></param>
        /// <returns>返回影响行数</returns>
        public static int ExecuteNonQuery(string sql, params SqlParameter[] p)
        {
            try
            {
                using (SqlConnection conn = new SqlConnection(connStr))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    if (p != null)
                    {
                        cmd.Parameters.AddRange(p);
                    }
                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }

        }

        public static Object ExcuteScalar(string sql, params SqlParameter[] p)
        {
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (p != null)
                {
                    cmd.Parameters.AddRange(p);
                }
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                return cmd.ExecuteScalar();
            }
        }
    }
}